package com.loongtech.bi.advertisment.service.impl;

import com.loongtech.bi.advertisment.model.*;
import com.loongtech.bi.advertisment.model.chart.AdvChartUIData1;
import com.loongtech.bi.advertisment.model.chart.AdvChartUIData2;
import com.loongtech.bi.advertisment.model.chart.AdvChartUIData3;
import com.loongtech.bi.advertisment.model.chart.AdvChartUIData4;
import com.loongtech.bi.advertisment.service.PayUserService;
import com.loongtech.bi.dao.CommonDao;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service("payUserService")
public class PayUserServiceImpl implements PayUserService {

	@Resource(name = "commonDao")
	private CommonDao commonDao;

	@Override
	public List<PayHourData> getListHourData(String day, String[] selectedAgent, String[] selectedPlatform)
			throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select 'Total' dateStr,sum(tdclick) clkEquipment,sum(tdactivate) actEquipment, sum(newactive)newActive, sum(newmac) newEquipment, ");
		sql.append("case when sum(newactive) = 0 then 0 else round(sum(newmac)/sum(newactive),2)end AccountActive, sum(newuser) newAccount,sum(payuser) newPayAccount,sum(paymoney) newPay ,  ");
		sql.append("case when sum(newuser) = 0 then 0 else round(sum(payuser)/sum(newuser),2)end newPayRate, ");
		sql.append("case when sum(newuser) = 0 then 0 else round(sum(paymoney)/sum(newuser),2)end arpu ");
		sql.append("from td_adv_statistics t ");
		sql.append("left join bi_advconfig bi on t.url = bi.id ");
		sql.append("where logdate = '" + day + "'  ");
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("union ");
		sql.append("select concat(left(starttime,2),':00~' , left(starttime,2) , ':59')dateStr, ");
		sql.append("sum(tdclick)clkEquipment,sum(tdactivate)actEquipment, sum(newactive)newActive, sum(newmac)newEquipment, ");
		sql.append("case when sum(newactive) = 0 then 0 else round(sum(newmac)/sum(newactive),2)end AccountActive, sum(newuser)newAccount,sum(payuser)newPayAccount, sum(paymoney)newPay, ");
		sql.append("case when sum(newuser) = 0 then 0 else round(sum(payuser)/sum(newuser),2)end newPayRate, ");
		sql.append("case when sum(newuser) = 0 then 0 else round(sum(paymoney)/sum(newuser),2)end arpu ");

		sql.append("from td_adv_statistics t ");
		sql.append("left join bi_advconfig bi on t.url = bi.id ");
		sql.append("where logdate = ?  ");
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by left(starttime,2) order by dateStr desc");
		return commonDao.queryObjList(sql.toString(), new Object[] { day }, PayHourData.class);
	}

	@Override
	public List<PayMinData> getListMinData(String day, String[] selectedId, String[] selectedAffiliation,
			String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype,
			String[] selectedAdvPosition) throws Exception {
		List<Object> objs = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		sql.append("select 'Filtrate Total' as dateStr,'' as id,'' as advName, ");
		sql.append("sum(tdclick) clkEquipment,sum(tdactivate) actEquipment,sum(newactive)newActive, ");
		sql.append("sum(newmac) newEquipment,case when sum(newactive) = 0 then 0 else round(sum(newmac)/sum(newactive),2)end AccountActive, sum(newuser) newAccount,sum(payuser) newPayAccount,sum(paymoney) newPay  ");
		sql.append("from td_adv_statistics t ");
		sql.append("left join bi_advconfig bi on t.url = bi.id ");
		sql.append("where logdate = ?   ");
		objs.add(day);
		if (selectedId != null && selectedId.length != 0) {
			sql.append("and id in (" + getParams(selectedId) + ")");
		}
		if (selectedAffiliation != null && selectedAffiliation.length != 0) {
			sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
		}
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
			sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
		}
		sql.append(" union all ");
		sql.append("(select concat(starttime,'~',endtime)dateStr,ifnull(t.url,'') id, ");
		sql.append("ifnull(bi.advPosition,t.url) advName,ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment, ");
		sql.append("ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, case when newactive is null then 0 else round(ifnull(newmac,0)/newactive,2)end AccountActive, ifnull(newuser,0) newAccount,ifnull(payuser,0) newPayAccount,ifnull(paymoney,0) newPay  ");
		sql.append("from td_adv_statistics t ");
		sql.append("left join bi_advconfig bi on t.url = bi.id ");
		sql.append("where logdate = ?   ");
		objs.add(day);
		if (selectedId != null && selectedId.length != 0) {
			sql.append("and id in (" + getParams(selectedId) + ")");
		}
		if (selectedAffiliation != null && selectedAffiliation.length != 0) {
			sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
		}
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
			sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
		}
		sql.append("order by starttime desc ,url) ");
		return commonDao.queryObjList(sql.toString(), objs.toArray(), PayMinData.class);
	}

	private String getParams(String[] param) throws Exception {
		StringBuilder s = new StringBuilder();
		for (String str : param) {
			s.append("'" + str + "',");
		}
		return s.substring(0, s.length() - 1);
	}

	@Override
	public List<ChannelRtData> getListChannelData(String day, String[] selectedAffiliation,
			String[] selectedChannelName, String[] selectedAgent, String[] selectedPlatform, String[] selectedPaytype,
			String[] selectedAdvPosition) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select bi.channelName,ifnull(bi.agent,'') agentName,sum(a.clkEquipment)clkEquipment, sum(a.actEquipment)actEquipment,sum(a.newActive)newActive,sum(a.newEquipment)newEquipment,  ");
		sql.append("case when sum(newActive) = 0 then 0 else round(sum(newEquipment)/sum(newActive),2)end AccountActive,sum(a.newAccount)newAccount, sum(a.newPayAccount)newPayAccount,sum(a.newPay)newPay,  ");
		sql.append("case when sum(a.newAccount) = 0 then 0 else round(sum(a.newPayAccount)/sum(a.newAccount),2)end newPayRate,  ");
		sql.append("case when sum(a.newAccount) = 0 then 0 else round(sum(a.newPay)/sum(a.newAccount),2)end arpu ");
		sql.append(" from ( ");
		sql.append("select url,sum(tdclick)clkEquipment,sum(tdactivate)actEquipment,sum(newactive)newActive, ");
		sql.append("sum(newmac)newEquipment,case when sum(newactive) = 0 then 0 else round(sum(newmac)/sum(newactive),2)end AccountActive,sum(newuser)newAccount, ");
		sql.append("sum(payuser)newPayAccount,sum(paymoney) newPay ");
		sql.append("from td_adv_statistics  ");
		sql.append("where logdate = ?  ");
		sql.append("group by url)a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id ");
		sql.append("where 1=1 and bi.channelName is not null ");
		if (selectedAffiliation != null && selectedAffiliation.length != 0) {
			sql.append("and affiliation in (" + getParams(selectedAffiliation) + ")");
		}
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		if (selectedPaytype != null && selectedPaytype.length != 0) {
			sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
		}
		if (selectedAdvPosition != null && selectedAdvPosition.length != 0) {
			sql.append("and advPosition in (" + getParams(selectedAdvPosition) + ")");
		}
		sql.append("group by bi.channelName,bi.agent ");
		return commonDao.queryObjList(sql.toString(), new Object[] { day }, ChannelRtData.class);
	}

	/**
	 * 
	 */
	@Override
	public List<PayMinData> getChannelDetailData(String day, String channelName, String agentName) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select concat(starttime,'~',endtime)dateStr, ");
		sql.append("ifnull(t.url,'') url, ifnull(bi.actName,t.url) advName, ifnull(tdclick,0) clkEquipment,ifnull(tdactivate,0) actEquipment, ");
		sql.append("ifnull(newactive,0) newActive , ifnull(newmac,0) newEquipment, case when newactive is null then 0 else round(ifnull(newmac,0)/newactive,2)end AccountActive,  ifnull(newuser,0) newAccount,ifnull(payuser,0) newPayAccount,ifnull(paymoney,0) newPay,  ");
		sql.append("case when newuser = 0 then 0 else round(payuser/newuser,2)end newPayRate, ");
		sql.append("case when newuser = 0 then 0 else round(paymoney/newuser,2)end arpu ");

		sql.append("from td_adv_statistics t ");
		sql.append("left join bi_advconfig bi on t.url = bi.id ");
		sql.append("where logdate = ?   ");
		sql.append("and channelName = ?  ");
		sql.append("and agent = ?  ");
		sql.append("order by starttime desc ,url ");
		return commonDao.queryObjList(sql.toString(), new Object[] { day, channelName, agentName }, PayMinData.class);
	}

	@Override
	public List<AdvWarningData> getListWarningData(String sday, String eday, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String seltype) throws Exception {
		StringBuilder sql = new StringBuilder();
		List<Object> lo = new ArrayList<>();
		sql.append("select a.logday , channelName , agent, sum(te)active , sum(tw)warnActice,  ");
		sql.append("case when sum(te) = 0 then 0 else round(sum(tw)/sum(te),4) end rate from( ");
		sql.append("select  a.logday,a.spreadurl url , ifnull(a.te,0)te , ifnull(w.tw,0)tw from ( ");
		sql.append("select logday,spreadurl,count(1)te  ");
		sql.append("from realtime_tdevent t  ");
		sql.append("where logday >= ? and logday<=? ");
		lo.add(sday);
		lo.add(eday);
		sql.append("and spreadurl !=''  ");
		sql.append("group by  logday , spreadurl)a ");
		sql.append("left join ( ");
		sql.append("select  t.logday,spreadurl,count(1)tw  from realtime_tdevent t ");
		sql.append("inner join ( ");

		sql.append("select mac from td_adv_warning_mac where 1 = 1  ");

		if ("-1".equals(seltype)) {
			sql.append("and (logday >= ? and logday<= ?) or logday = 0  ");
			lo.add(sday);
			lo.add(eday);
		}
		if ("3".equals(seltype)) {
			sql.append("and logday = 0  ");
		}
		if ("1".equals(seltype) || "2".equals(seltype)) {
			sql.append("and warntype = " + seltype + "  ");
			sql.append("and logday >= ? and logday<=? ");
			lo.add(sday);
			lo.add(eday);
		}
		sql.append("group by mac ");
		sql.append(")a on t.deviceid = a.mac ");
		sql.append("where logday >= ? and logday<=? and spreadurl !=''  ");
		lo.add(sday);
		lo.add(eday);
		sql.append("group by logday,spreadurl)w ");
		sql.append("on a.spreadurl = w.spreadurl and a.logday = w.logday )a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by a.logday , channelName , agent ");
		sql.append("order by a.logday desc , active desc ");
		return commonDao.queryObjList(sql.toString(), lo.toArray(), AdvWarningData.class);
	}

	@Override
	public List<List<List<String>>> getExcelWarningData(String sday, String eday, String[] selectedChannelName,
			String[] selectedAgent, String[] selectedPlatform, String seltype) throws Exception {
		List<List<List<String>>> res = new ArrayList<List<List<String>>>();
		StringBuilder sql = new StringBuilder();
		List<Object> lo = new ArrayList<>();
		sql.append("select a.logday , channelName , agent, sum(te)active , sum(tw)warnActice,  ");
		sql.append("case when sum(te) = 0 then 0 else round(sum(tw)/sum(te),4) end rate from( ");
		sql.append("select  a.logday,a.spreadurl url , ifnull(a.te,0)te , ifnull(w.tw,0)tw from ( ");
		sql.append("select logday,spreadurl,count(1)te  ");
		sql.append("from realtime_tdevent t  ");
		sql.append("where logday >= ? and logday<=? ");
		lo.add(sday);
		lo.add(eday);
		sql.append("and spreadurl !=''  ");
		sql.append("group by  logday , spreadurl)a ");
		sql.append("left join ( ");
		sql.append("select  t.logday,spreadurl,count(1)tw  from realtime_tdevent t ");
		sql.append("inner join ( ");
		sql.append("select mac from td_adv_warning_mac where 1 = 1  ");
		if ("-1".equals(seltype)) {
			sql.append("and (logday >= ? and logday<= ?) or logday = 0  ");
			lo.add(sday);
			lo.add(eday);
		}
		if ("3".equals(seltype)) {
			sql.append("and logday = 0  ");
		}
		if ("1".equals(seltype) || "2".equals(seltype)) {
			sql.append("and warntype = " + seltype + "  ");
			sql.append("and logday >= ? and logday<=? ");
			lo.add(sday);
			lo.add(eday);
		}
		sql.append("group by mac ");
		sql.append(")a on t.deviceid = a.mac ");
		sql.append("where logday >= ? and logday<=? and spreadurl !=''  ");
		lo.add(sday);
		lo.add(eday);
		sql.append("group by logday,spreadurl)w ");
		sql.append("on a.spreadurl = w.spreadurl and a.logday = w.logday )a ");
		sql.append("left join bi_advconfig bi on a.url = bi.id  ");
		if (selectedChannelName != null && selectedChannelName.length != 0) {
			sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
		}
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by a.logday , channelName , agent ");
		sql.append("order by a.logday desc , active desc ");
		List<List<String>> re1 = commonDao.queryListData(sql.toString(), lo.toArray());
		lo.clear();
		StringBuilder sql1 = new StringBuilder();
		sql1.append("select logday, mac from td_adv_warning_mac where 1 = 1  ");

		if ("-1".equals(seltype)) {
			sql.append("and (logday >= ? and logday<= ?) or logday = 0  ");
			lo.add(sday);
			lo.add(eday);
		}
		if ("3".equals(seltype)) {
			sql.append("and logday = 0  ");
		}
		if ("1".equals(seltype) || "2".equals(seltype)) {
			sql.append("and warntype = " + seltype + "  ");
			sql.append("and logday >= ? and logday<=? ");
			lo.add(sday);
			lo.add(eday);
		}
		sql1.append("group by  logday , mac ");
		List<List<String>> re2 = commonDao.queryListData(sql1.toString(), new Object[] { sday, eday });
		res.add(re1);
		res.add(re2);
		return res;
	}

	@Override
	public List<AdvWarnMacData> getWarnMacData(Integer gameId) throws Exception {
		StringBuilder sql = new StringBuilder();
		if (gameId != null) {
			sql.append("select mac,kindtype,source from td_adv_warning_mac where warntype = 3 and gameId = " + gameId);
		} else {
			sql.append("select mac,kindtype,source from td_adv_warning_mac where warntype = 3 ");
		}
		return commonDao.queryObjList(sql.toString(), null, AdvWarnMacData.class);
	}

	@Override
	public void batchAddMac(List<AdvWarnMacData> datas) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into td_adv_warning_mac(warntype,mac,kindtype,source) values  ");
		for (AdvWarnMacData data : datas) {
			sql.append("(3,'" + data.getMac() + "','" + data.getKindtype() + "','" + data.getSource() + "'),");
		}
		commonDao.addOrUpdate(sql.substring(0, sql.length() - 1), null);
	}

	@Override
	public List<AdvWarningInputData> getListWarningInputData(String sday, String eday) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from(select ifnull(a.createday,b.createday)logday, ifnull(a.kindtype,b.kindtype)kindtype, ifnull(a.source,b.source)source, ifnull(a.cm,0)active, ifnull(b.cm,0)newMac  from ( ");
		sql.append("select t.createday,m.kindtype,m.source,count(distinct mac)cm from totalsteplogreport t ");
		sql.append("left join td_adv_warning_mac m on m.mac = t.deviceid  and m.warntype =3 ");
		sql.append("where m.mac is not null ");
		sql.append("and t.createday >= ? and t.createday <= ? ");
		sql.append("group by t.createday,m.kindtype,m.source)a ");
		sql.append("left join ");
		sql.append("(select min(t.createday)createday,m.kindtype,m.source,count(distinct m.mac)cm from totalmaccreate t ");
		sql.append("left join td_adv_warning_mac m on m.mac = t.mac and m.warntype =3  ");
		sql.append("where m.mac is not null ");
		sql.append("and t.createday >= ? and t.createday <= ? ");
		sql.append("group by m.kindtype,m.source)b ");
		sql.append("on a.createday=b.createday and a.kindtype=b.kindtype and a.source=b.source ");
		sql.append("union all ");
		sql.append("select ifnull(a.createday,b.createday)createday, ifnull(a.kindtype,b.kindtype)kindtype, ifnull(a.source,b.source)source, ifnull(a.cm,0)active, ifnull(b.cm,0)newMac  from ( ");
		sql.append("select t.createday,m.kindtype,m.source,count(distinct mac)cm from totalsteplogreport t  ");
		sql.append("left join td_adv_warning_mac m on m.mac = t.deviceid  and m.warntype =3  ");
		sql.append("where m.mac is not null  ");
		sql.append("and t.createday >= ? and t.createday <= ? ");
		sql.append("group by t.createday,m.kindtype,m.source)a ");
		sql.append("right join  ");
		sql.append("(select min(t.createday)createday,m.kindtype,m.source,count(distinct m.mac)cm from totalmaccreate t  ");
		sql.append("left join td_adv_warning_mac m on m.mac = t.mac  and m.warntype =3  ");
		sql.append("where m.mac is not null  ");
		sql.append("and t.createday >= ? and t.createday <= ? ");
		sql.append("group by m.kindtype,m.source)b ");
		sql.append("on a.createday=b.createday and a.kindtype=b.kindtype and a.kindtype=b.kindtype ");
		sql.append(")a group by logday,kindtype,source ");
		return commonDao.queryObjList(sql.toString(), new Object[] { sday, eday, sday, eday, sday, eday, sday, eday },
				AdvWarningInputData.class);
	}

	@Override
	public List<AdvChartUIData1> getAdvActDate(String day, String lastday, String[] selectedAgent,
			String[] selectedPlatform) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select left(starttime,2) hour , '" + day + "' day ,'" + lastday + "' lastday,   ");
		sql.append("sum(case when logdate = '" + day + "' then tdactivate end) activeMacLookValue, ");
		sql.append("sum(case when logdate = '" + lastday + "' then tdactivate end) activeMacCompareValue ");
		sql.append("from td_adv_statistics t left join bi_advconfig bi on t.url = bi.id where logdate in (?,?)  ");
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by left(starttime,2) order by hour desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { day, lastday }, AdvChartUIData1.class);
	}

	@Override
	public List<AdvChartUIData2> getAdvActMacDate(String day, String lastday, String[] selectedAgent,
			String[] selectedPlatform) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select left(starttime,2) hour , '" + day + "' day ,'" + lastday + "' lastday,   ");
		sql.append("sum(case when logdate = '" + day + "' then newmac end) newMacLookValue, ");
		sql.append("sum(case when logdate = '" + lastday + "' then newmac end) newMacCompareValue ");
		sql.append("from td_adv_statistics t left join bi_advconfig bi on t.url = bi.id where logdate in (?,?)  ");
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by left(starttime,2) order by hour desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { day, lastday }, AdvChartUIData2.class);
	}

	@Override
	public List<AdvChartUIData3> getAdvActUserDate(String day, String lastday, String[] selectedAgent,
			String[] selectedPlatform) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select left(starttime,2) hour , '" + day + "' day ,'" + lastday + "' lastday,   ");
		sql.append("sum(case when logdate = '" + day + "' then newuser end) newUserLookValue, ");
		sql.append("sum(case when logdate = '" + lastday + "' then newuser end) newUserCompareValue ");
		sql.append("from td_adv_statistics t left join bi_advconfig bi on t.url = bi.id where logdate in (?,?)  ");
		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by left(starttime,2) order by hour desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { day, lastday }, AdvChartUIData3.class);
	}

	@Override
	public List<AdvChartUIData4> getAdvActPayDate(String day, String lastday, String[] selectedAgent,
			String[] selectedPlatform) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select left(starttime,2) hour , '" + day + "' day ,'" + lastday + "' lastday,   ");
		sql.append("sum(case when logdate = '" + day + "' then paymoney end) newPayLookValue, ");
		sql.append("sum(case when logdate = '" + lastday + "' then paymoney end) newPayCompareValue ");
		sql.append("from td_adv_statistics t left join bi_advconfig bi on t.url = bi.id where logdate in (?,?)  ");

		if (selectedAgent != null && selectedAgent.length != 0) {
			sql.append("and agent in (" + getParams(selectedAgent) + ")");
		}
		if (selectedPlatform != null && selectedPlatform.length != 0) {
			sql.append("and platform in (" + getParams(selectedPlatform) + ")");
		}
		sql.append("group by left(starttime,2) order by hour desc ");
		return commonDao.queryObjList(sql.toString(), new Object[] { day, lastday }, AdvChartUIData4.class);
	}
}
